﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using BCNPCObject.DB;
using DbLinq;
using DbLinq.Oracle;
using CMISLibrary.Entities;
using CMISLibrary;

namespace BCNPCObject.EXCEL
{
    public class cls_NPC_CHAMNO_OFFLINE_Controller
    {

        #region   Atributes

        //private DataSet CMIS_Header = new CMISOutputParameter();
        private NPC_CHAMNO_OFFLINE info;

        #endregion

        #region   Constructor
        public cls_NPC_CHAMNO_OFFLINE_Controller()
        {
            info = new NPC_CHAMNO_OFFLINE();
        }
        public cls_NPC_CHAMNO_OFFLINE_Controller(NPC_CHAMNO_OFFLINE Info)
        {
            this.info = Info;
        }

        #endregion

        #region   Properties

        public NPC_CHAMNO_OFFLINE pInfor
        {
            get { return info; }
            set { info = value; }
        }
        #endregion
        #region Methods
        public bool insert_NPC_CHAMNO_OFFLINE(ref string maloi)
        {
            
            long _ID = CMIS2.DB.ExecuteCommand("Select SEQ_NPC_CHAMNO_OFFLINE.NEXTVAL from dual");
            if (_ID == -1) return false;
            // xóa trước khi nhận
            if (!delete_NPC_CHAMNO_OFFLINE(info.MA_DVIQLY, info.MA_KHANG, info.ID_HDON, info.THANG_HT, info.NAM_HT, ref maloi))
            {
                //return false;
            }
            try
            {
                info.ID_OFFLINE = Convert.ToInt64(_ID);
                //info.TINHTRANGIN = "0";
                info.NGAY_TAO = System.DateTime.Now;
                NPC_CHAMNO_OFFLINE objNPC_CHAMNO_OFFLINE = new NPC_CHAMNO_OFFLINE()
                {
                    MA_DVIQLY = info.MA_DVIQLY,
                    MA_KHANG = info.MA_KHANG,
                    ID_OFFLINE = info.ID_OFFLINE,
                    ID_HDON = info.ID_HDON,
                    TIEN_NOP = info.TIEN_NOP,
                    THUE_NOP = info.THUE_NOP,
                    SO_BBGIAO = info.SO_BBGIAO,
                    MA_TNGAN = info.MA_TNGAN,
                    MA_TNGAN_THUHO = info.MA_TNGAN_THUHO,
                    NGAY_CHAMNO = info.NGAY_CHAMNO,
                    NAM_HT = info.NAM_HT,
                    THANG_HT = info.THANG_HT,
                    TRANGTHAI = info.TRANGTHAI,
                    NGAY_TAO = info.NGAY_TAO,
                    NGUOI_TAO = info.NGUOI_TAO,
                    TINHTRANGIN = info.TINHTRANGIN,
                    FMIS = info.FMIS,
                };
                CMIS2.DB.NPC_CHAMNO_OFFLINE.InsertOnSubmit(info);
                CMIS2.DB.SubmitChanges();
                //id_ls_treothao = _ID;
                CMIS2.ResetDB();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool delete_NPC_CHAMNO_OFFLINE(string ma_dviqly,string ma_khang,Int64 id_hdon,int thanght,int namht, ref string maloi)
        {
            try
            {
                var query = from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                            where (p.MA_DVIQLY == ma_dviqly && p.MA_KHANG == ma_khang && p.ID_HDON == id_hdon && p.THANG_HT == thanght && p.NAM_HT == namht)
                                    select p;
                if (query != null && query.Count() > 0)
                {
                    CMIS2.DB.NPC_CHAMNO_OFFLINE.DeleteAllOnSubmit(query.ToList());
                    CMIS2.DB.SubmitChanges();
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB(); return false;
            }
        }
        public bool update_NPC_CHAMNO_OFFLINE_NHOM(string ma_dviqly,string ma_khang, Int64 id_hdon, int thanght, int namht, Int64 nhom, ref string maloi)
        {
            try
            {
                var query = from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                            where (p.MA_DVIQLY == ma_dviqly && p.MA_KHANG == ma_khang && p.ID_HDON == id_hdon && p.THANG_HT == thanght && p.NAM_HT == namht)
                            select p;

                foreach (NPC_CHAMNO_OFFLINE ord in query)
                {
                    //ord.MA_KHANG = ma_khang;
                    ord.GROUP_NHOM = nhom;
                }

                // Submit the changes to the database.
                CMIS2.DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool update_NPC_CHAMNO_OFFLINE_DUYET(string ma_dviqly, string ma_khang, Int64 id_hdon, int thanght, int namht, ref string maloi)
        {
            try
            {
                var query = from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                            where (p.MA_DVIQLY == ma_dviqly && p.MA_KHANG == ma_khang && p.ID_HDON == id_hdon && p.THANG_HT == thanght && p.NAM_HT == namht)
                            select p;

                foreach (NPC_CHAMNO_OFFLINE ord in query)
                {
                    //ord.MA_KHANG = ma_khang;
                    ord.TRANGTHAI = 1;
                }

                // Submit the changes to the database.
                CMIS2.DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool update_chamno_OFFLINE(string ma_dviqly,Int64 id_hdon)
        {
            try
            {
                var query = from p in CMIS2.DB.CN_THEODOINO
                            where (p.MA_DVIQLY == ma_dviqly && p.ID_HDON == id_hdon && p.TTRANG_SSAI == 0)
                            select p;

                foreach (CN_THEODOINO ord in query)
                {
                    ord.TIEN_NO = 0;
                    ord.THUE_NO = 0;
                }
                CMIS2.DB.SubmitChanges();
                return true;
            }
            catch
            { return false; }
        }
        public bool check_duyetchamno(string ma_dviqly,Int64 id_hdon,string ma_tngan)
        {
            try
            {
                var query = (from p in CMIS2.DB.CN_HDON_PHTHU
                            where (p.MA_DVIQLY == ma_dviqly && p.ID_HDON == id_hdon && p.MA_TNGAN == ma_tngan)
                            select p).ToList();
                if (query.Count != 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool update_NPC_CHAMNO_OFFLINE_IN(string ma_dviqly,string ma_tngan, int thanght, int namht,DateTime? ngaynop,DateTime gionop,ref int trangthai, ref string maloi)
        {
            try
            {
                var tt = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                         where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN != "0")
                         select p).ToList();
                if (tt.Count != 0)
                {
                    Int32 _ngay_TT = (from q2 in tt select q2).AsEnumerable().Max(m => Convert.ToInt32(m.TINHTRANGIN));
                    var query = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN == "0")
                                select p).ToList();
                    if (query.Count != 0)
                    {
                        _ngay_TT++;
                        foreach (NPC_CHAMNO_OFFLINE ord in query)
                        {
                            //ord.MA_KHANG = ma_khang;
                            ord.TINHTRANGIN =_ngay_TT.ToString();
                            ord.NGAY_TAO = gionop;
                        }
                        CMIS2.DB.SubmitChanges();
                        trangthai = _ngay_TT;
                        return true;
                    }
                    else
                    {
                        trangthai = 0;
                        return false;

                    }
                    //thang = thangnam[0].THANG;
                    //nam = thangnam[0].NAM;
                }
                else
                {
                    var query = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                 where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN == "0")
                                select p).ToList();
                    if (query.Count != 0)
                    {
                        foreach (NPC_CHAMNO_OFFLINE ord in query)
                        {
                            //ord.MA_KHANG = ma_khang;
                            ord.TINHTRANGIN = "1";
                            ord.NGAY_TAO = gionop;
                        }
                        // Submit the changes to the database.
                        CMIS2.DB.SubmitChanges();
                        trangthai = 1;
                        return true;
                    }
                    else
                    {
                        trangthai = 0;
                        return false;
                    }
                    
                }
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public bool update_NPC_CHAMNO_OFFLINE_XOA(string ma_dviqly, string ma_tngan, int thanght, int namht, DateTime? ngaynop,int trangthaixoa,int trangthaimax, ref string maloi)
        {
            try
            {
                string strupdate = trangthaixoa.ToString();
                var query = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN == strupdate)
                             select p).ToList();
                if (query.Count != 0)
                {
                    foreach (NPC_CHAMNO_OFFLINE ord in query)
                    {
                        //ord.MA_KHANG = ma_khang;
                        ord.TINHTRANGIN = "0";
                        //ord.NGAY_TAO = gionop;
                    }
                    // Submit the changes to the database.
                    CMIS2.DB.SubmitChanges();
                    for (int i = trangthaixoa + 1; i <= trangthaimax; i++)
                    {
                        strupdate = i.ToString();
                        var update = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                      where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN == strupdate)
                                     select p).ToList();
                        foreach (NPC_CHAMNO_OFFLINE ord in update)
                        {
                            //ord.MA_KHANG = ma_khang;
                            ord.TINHTRANGIN = (i - 1).ToString();
                            //ord.NGAY_TAO = gionop;
                        }
                        CMIS2.DB.SubmitChanges();
                    }
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public DataSet select_lannoptien(string ma_dviqly, string ma_tngan, int thanght, int namht, DateTime? ngaynop, ref string maloi)
        {
            try
            {
                DB.CMIS2.ResetDB();
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                var tt = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                          where (p.MA_DVIQLY == ma_dviqly && p.NGAY_CHAMNO == ngaynop && p.MA_TNGAN.ToUpper() == ma_tngan.ToUpper() && p.THANG_HT == thanght && p.NAM_HT == namht && p.TINHTRANGIN != "0")
                          select p).ToList();

                var result = from ds1 in tt
                           group ds1 by new { ds1.TINHTRANGIN} into grp1
                           orderby grp1.Key.TINHTRANGIN
                           select new
                           {
                               STT = grp1.Key.TINHTRANGIN,
                               THOIDIEM = "Chốt lần :" + grp1.Key.TINHTRANGIN.ToString(),
                           };
                dt = Utility.LINQToDataTable(result);
                ds.Tables.Add(dt);
                return ds;
            }
            catch(Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return null;
            }
        }
        public DataSet Get_KhachHang(string ma_dviqly, string ma_khang,int thanght, int namht,ref string messger, ref string maloi)
        {
            try
            {
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                Int64[] arrID_HDON;

                // tìm khách hàng
                var khachhang = (from kh in CMIS2.DB.CN_THEODOINO
                             where kh.MA_DVIQLY == ma_dviqly && kh.MA_KHANG == ma_khang
                             select kh).ToList();
                if (khachhang == null || khachhang.Take(1).Count() == 0)
                {
                    var hoadon = (from cs in CMIS2.DB.HDN_HDON
                                 where cs.MA_DVIQLY == ma_dviqly && cs.MA_KHANG == ma_khang && cs.THANG == thanght && cs.NAM == namht
                                 select cs).ToList();
                    var chiso = (from cs in CMIS2.DB.GCS_CHISO
                                 where cs.MA_DVIQLY == ma_dviqly && cs.MA_KHANG == ma_khang && cs.LOAI_CHISO == "DDK" && cs.THANG == thanght && cs.NAM == namht
                                 select cs).ToList();
                    if (hoadon == null || hoadon.Take(1).Count() == 0)
                    {
                        if (chiso == null || chiso.Take(1).Count() == 0)
                        {
                            messger = "Khách hàng chưa được nhập chỉ số!";
                        }
                        else
                        {
                            messger = "Khách hàng mới nhập chỉ số, chưa được tính hóa đơn!";
                        }
                    }
                    else
                    {
                        string a = hoadon.Select(b => b.NGAY_PHANH).FirstOrDefault().ToString();
                        if (a.Trim() == "")
                        {
                            messger = "Hóa đơn chưa được giao cho thu ngân viên!";
                        }
                    }
                    return null;
                }
                else
                {
                    
                    arrID_HDON = khachhang.Select(c => c.ID_HDON).ToArray();
                }
                var ThuNgan = (from t in CMIS2.DB.D_TNGAN_VIEN.ToList()
                               where t.MA_DVIQLY == ma_dviqly && t.TINH_TRANG == 1
                               select t).ToList();
                // tìm thu ngan theo số bb giao
                var BB_TN = (from b in ThuNgan.ToList()
                             join tn in CMIS2.DB.CN_BB_GTHU.ToList() on new { b.MA_DVIQLY, b.MA_TNGAN }
                                                   equals new { tn.MA_DVIQLY, tn.MA_TNGAN }
                             select new
                             {
                                 tn.MA_DVIQLY,
                                 tn.MA_TNGAN,
                                 b.TEN_TNGAN,
                                 tn.SO_BBGIAO,
                                 tn.NGAY_GIAO,
                             }).ToList();
                //lấy hóa đơn theo biên bản giao
                var hdon =  from c in CMIS2.DB.CN_HDON_GTHU
                             where c.MA_DVIQLY == ma_dviqly
                             && arrID_HDON.Contains(c.ID_HDON)
                             select c;
                var HDON_BB_TN = (from c in hdon.ToList()
                                  //where c.MA_DVIQLY == ma_dviqly
                                  join bbtn in BB_TN on new { c.MA_DVIQLY, c.SO_BBGIAO }
                                                   equals new { bbtn.MA_DVIQLY, bbtn.SO_BBGIAO } into bbtn0
                                  from bbtn in bbtn0.DefaultIfEmpty()
                                  select new { 
                                  c.MA_DVIQLY,
                                  c.ID_HDON,
                                  c.MA_SOGCS,
                                  c.SO_BBGIAO,
                                  bbtn.MA_TNGAN,
                                  bbtn.TEN_TNGAN,
                                  bbtn.NGAY_GIAO,
                                  }).ToList();
                var result = from p in khachhang
                             join nv in HDON_BB_TN on new { p.MA_DVIQLY, p.ID_HDON }
                                                                    equals new { nv.MA_DVIQLY, nv.ID_HDON } into nv0
                              from nv in nv0.DefaultIfEmpty()
                             select new
                             {
                                 p.MA_KHANG,
                                 p.TEN_KHANG,
                                 p.DCHI_KHANG,
                                 KYTHANGNAM = p.KY + " - " + p.THANG + " / " + p.NAM,
                                 p.TTHAI_GIAO,
                                 nv.MA_DVIQLY,
                                 nv.ID_HDON,
                                 nv.MA_SOGCS,
                                 nv.SO_BBGIAO,
                                 nv.MA_TNGAN,
                                 nv.TEN_TNGAN,
                                 NGAY_GIAO = nv.NGAY_GIAO.ToString("dd/MM/yyyy"),
                                 TONGTIENPS = (p.TIEN_PSINH + p.THUE_PSINH).ToString("#,###,###,###"),
                                 TONGTIENNO = (p.TIEN_NO + p.THUE_NO).ToString("#,###,###,###"),
                             };
                //string a = khachhang.Select(b => b.NGAY_PHANH).FirstOrDefault().ToString();
                Decimal a1 = Convert.ToDecimal(khachhang.Select(b1 => b1.TIEN_NO).FirstOrDefault());
                    if (a1 == 0)
                    {
                        messger = "Hóa đơn đã được chấm nợ!";
                    }
                    else
                    {
                        messger = "1";
                    }
                dt = Utility.LINQToDataTable(result);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return null;
            }
        }
        public bool check_ATM(string ma_dviqly, Int64 id_hdon)
        {
            try
            {
                var query = (from p in CMIS2.DB.NPC_GDICH_ATM
                             where (p.MA_DVIQLY == ma_dviqly && p.ID_HDON == id_hdon && p.MA_GD == "")
                             select p).ToList();
                if (query.Count != 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                CMIS2.ResetDB();
                return false;
            }
        }

        public DataSet check_thongtinkhachhang(string ma_khang,string ma_dviqly)
        {
            try
            {
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                var kh = (from a in CMIS2.DB.HDG_KHACH_HANG
                          where a.MA_KHANG == ma_khang && a.MA_DVIQLY == ma_dviqly
                          select a).ToList();
                if (kh.Count != 0)
                {
                    if (kh[0].DTHOAI.ToString() == "")
                    {
                        dt = Utility.LINQToDataTable(kh);
                        ds.Tables.Add(dt);
                    }
                    return ds;
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }
        // lấy số liệu khách hàng excel

        //public DataSet GetKhangExcel(string sma_dviqly, string sma_khang, Int64 sid_hdon)
        //{
            //try
            //{
            //    var khachhang = (from kh in CMIS2.DB.CN_THEODOINO
            //                     where kh.MA_DVIQLY == sma_dviqly && kh.MA_KHANG == sma_khang && kh.ID_HDON == sid_hdon
            //                     select kh).ToList();
            //    if (khachhang == null || khachhang.Take(1).Count() == 0)
            //    {
 
            //    }
            //}
            //catch
            //{ }
        //}


        #region tungdt - báo cáo tồn và báo cáo ATM
        public DataSet CN_So_BBGiao_Nutgoc(string ma_tngan, string ma_dvqly)
        {
            DataSet ds = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0)
            {
                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO)
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var id = (from b in CMIS2.DB.CN_HDON_GTHU
                              where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                              select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

                    var ketqua = (from e in CMIS2.DB.CN_THEODOINO
                                  where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly
                                  select new
                                  {
                                      e.KY,
                                      e.MA_SOGCS,
                                      TTIEN_NOP = e.TIEN_PSINH + e.THUE_PSINH,
                                      TIEN_NOP = e.TIEN_PSINH,
                                      THUE_NOP = e.THUE_PSINH,
                                      e.ID_HDON,
                                      e.THANG,
                                      e.NAM,
                                      e.MA_KHANG,
                                      e.LOAI_HDON,
                                      e.TEN_KHANG,
                                      e.DCHI_KHANG
                                  }).ToList();
                    var kq = (from c in ketqua
                              join d in id on c.ID_HDON equals d.ID_HDON
                              select new
                              {
                                  d.SO_BBGIAO,
                                  d.MA_SOGCS,
                                  TONG_TIEN = c.TTIEN_NOP,
                                  TIEN_NO = c.TIEN_NOP,
                                  THUE_NO = c.THUE_NOP,
                                  c.ID_HDON,
                                  c.THANG,
                                  c.NAM,
                                  c.MA_KHANG,
                                  c.LOAI_HDON,
                                  c.TEN_KHANG,
                                  c.DCHI_KHANG,
                                  c.KY
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);

                    return ds;
                }
                return null;
            } return null;
        }

        public DataSet CN_So_BBGiao_Nutcha(string ma_tngan, int so_BBGIAO, string ma_dvqly)
        {
            DataSet ds = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            #region Nutcha
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                         select b.ID_HDON).ToArray();

            var id = (from b in CMIS2.DB.CN_HDON_GTHU
                      where so_bbgiao.Contains(b.SO_BBGIAO)
                      select new { b.ID_HDON, b.SO_BBGIAO, b.MA_SOGCS }).ToList();

            var ketqua = (from e in CMIS2.DB.CN_THEODOINO
                          where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly
                          select new
                          {
                              e.KY,
                              e.MA_SOGCS,
                              TTIEN_NOP = e.TIEN_NO + e.THUE_NO,
                              TIEN_NOP = e.TIEN_NO,
                              THUE_NOP = e.THUE_NO,
                              e.ID_HDON,
                              e.THANG,
                              e.NAM,
                              e.MA_KHANG,
                              e.LOAI_HDON,
                              e.TEN_KHANG,
                              e.DCHI_KHANG
                          }).ToList();
            var kq = (from c in ketqua
                      join d in id on c.ID_HDON equals d.ID_HDON
                      where d.SO_BBGIAO == so_BBGIAO
                      select new
                      {
                          d.SO_BBGIAO,
                          d.MA_SOGCS,
                          TONG_TIEN = c.TTIEN_NOP,
                          TIEN_NO = c.TIEN_NOP,
                          THUE_NO = c.THUE_NOP,
                          c.ID_HDON,
                          c.THANG,
                          c.NAM,
                          c.MA_KHANG,
                          c.LOAI_HDON,
                          c.TEN_KHANG,
                          c.DCHI_KHANG,
                          c.KY
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            ds.Tables.Add(dt);
            #endregion
            return ds;
        }

        public DataSet CN_So_BBGiao_Nutcon(string ma_tngan, int so_BBGIAO, string Ma_SoGCS)
        {
            DataSet ds = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            #region Nutcha
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan
                         select a.SO_BBGIAO).ToArray();
            id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                         where so_bbgiao.Contains(b.SO_BBGIAO) && b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                         select b.ID_HDON).ToArray();

            var id = (from b in CMIS2.DB.CN_HDON_GTHU
                      where so_bbgiao.Contains(b.SO_BBGIAO) && b.SO_BBGIAO == so_BBGIAO && b.MA_SOGCS == Ma_SoGCS
                      select new { b.ID_HDON, so_BBGIAO, Ma_SoGCS }).ToList();

            var theodoino = (from e in CMIS2.DB.CN_THEODOINO
                             where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0
                             select e).ToList();
            var kq = (from c in theodoino
                      join d in id on c.ID_HDON equals d.ID_HDON
                      select new
                      {
                          d.so_BBGIAO,
                          d.Ma_SoGCS,
                          c.TIEN_NO,
                          c.THUE_NO,
                          c.ID_HDON,
                          c.THANG,
                          c.NAM,
                          c.MA_KHANG,
                          c.LOAI_HDON,
                          c.TEN_KHANG,
                          c.DCHI_KHANG,
                          c.KY,
                          TONG_TIEN = c.TIEN_NO + c.THUE_NO
                      }).ToList();
            DataTable dt = Utility.LINQToDataTable(kq);
            ds.Tables.Add(dt);
            #endregion
            return ds;
        }

        public DataSet CN_Select(string ma_tngan)
        {
            DataSet ds = new DataSet();
            Int64[] BBG;
            Int64[] hdon;
            BBG = (from a in CMIS2.DB.CN_BB_GTHU
                   where a.MA_TNGAN == ma_tngan
                   select a.SO_BBGIAO).ToArray();
            if (BBG.Count() > 0)
            {
                hdon = (from a in CMIS2.DB.CN_HDON_GTHU
                        where BBG.Contains(a.SO_BBGIAO)
                        select a.ID_HDON).ToArray();
                if (hdon.Count() > 0)
                {
                    var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                     where a.TIEN_NO > 0 && a.TTRANG_SSAI != 2 && hdon.Contains(a.ID_HDON)
                                     select a).ToList();
                    var hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                     where BBG.Contains(a.SO_BBGIAO)
                                     select new { a.ID_HDON, a.SO_BBGIAO, a.MA_SOGCS }).ToList();
                    var kq = (from a in theodoino
                              join b in hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.MA_KHANG,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  a.TIEN_NO,
                                  a.THUE_NO,
                                  TONG_TIEN = a.TIEN_NO + a.THUE_NO,
                                  a.KY,
                                  a.THANG,
                                  a.NAM,
                                  b.SO_BBGIAO,
                                  b.MA_SOGCS
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);

                    return ds;
                } return null;
            } return null;
        }

        public DataSet ATM_Ton_Select(string ma_dvqly)
        {
            Int64[] id_hdon;
            string[] ma_gd;

            DataSet ds = new DataSet();

            ma_gd = (from a in CMIS2.DB.NPC_GDICH_ATM
                     where a.MA_HUYGD == null && a.MA_DVIQLY == ma_dvqly
                     select a.MA_GD).ToArray();
            if (ma_gd.Count() > 0)
            {
                var NPC_HDON_TKHOAN = (from b in CMIS2.DB.NPC_HDON_TKHOAN
                                       where ma_gd.Contains(b.MA_GD) && b.MA_DVIQLY == ma_dvqly
                                       select new
                                       {
                                           b.ID_HDON,
                                           b.NGAY_NOP,
                                           b.MA_GD,
                                           b.TIEN_GTGT,
                                           b.SO_TIEN,
                                           TONG_TIEN = b.SO_TIEN + b.TIEN_GTGT
                                       }).ToList();

                id_hdon = (from a in NPC_HDON_TKHOAN
                           select a.ID_HDON).ToArray();
                if (id_hdon.Count() > 0)
                {
                    var cn_theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                        where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvqly
                                        select a).ToList();
                    var cn_hdon_gthu = (from a in CMIS2.DB.CN_HDON_GTHU
                                        where id_hdon.Contains(a.ID_HDON)
                                        select a).ToList();
                    var ketqua = (from a in NPC_HDON_TKHOAN
                                  join b in cn_theodoino on a.ID_HDON equals b.ID_HDON
                                  select new
                                  {
                                      a.ID_HDON,
                                      a.NGAY_NOP,
                                      a.TIEN_GTGT,
                                      a.SO_TIEN,
                                      a.TONG_TIEN,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      b.MA_SOGCS,
                                      b.MA_KHANG
                                  }).ToList();
                    var kq = (from a in ketqua
                              join b in cn_hdon_gthu on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  a.NGAY_NOP,
                                  a.TIEN_GTGT,
                                  a.SO_TIEN,
                                  a.TONG_TIEN,
                                  a.TEN_KHANG,
                                  a.DCHI_KHANG,
                                  a.MA_SOGCS,
                                  a.MA_KHANG,
                                  b.SO_BBGIAO
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);
                    return ds;
                }
                return null;
            }
            return null;
        }

        public DataSet ChamnoOff_All(DateTime ngaybd, DateTime ngaykt, int thanght, string ma_dvql)
        {
            DataSet ds = new DataSet();
            Int64[] id_hdon;
            Int64[] so_bbgiao;

            id_hdon = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                       where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.THANG_HT == thanght
                       select a.ID_HDON).ToArray();
            if (id_hdon.Count() > 0 && id_hdon != null)
            {
                var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                 where id_hdon.Contains(a.ID_HDON)
                                 select a).ToList();
                var chamno_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                      where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt
                                      select a).ToList();
                so_bbgiao = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt
                             select a.SO_BBGIAO).ToArray();
                if (so_bbgiao.Count() > 0 && so_bbgiao != null)
                {
                    var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                                  where so_bbgiao.Contains(a.SO_BBGIAO)
                                  select new { a.MA_SOGCS, a.ID_HDON }).ToList();
                    var ketqua = (from a in chamno_offline
                                  join b in theodoino on a.ID_HDON equals b.ID_HDON
                                  where a.MA_DVIQLY == ma_dvql
                                  select new
                                  {
                                      a.MA_KHANG,
                                      b.ID_HDON,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      a.TIEN_NOP,
                                      a.THUE_NOP,
                                      a.NGAY_CHAMNO,
                                      TTIEN = a.TIEN_NOP + a.THUE_NOP,
                                      a.SO_BBGIAO,
                                      a.MA_TNGAN,
                                      a.THANG_HT
                                  }).ToList();
                    var kq = (from a in bbgiao
                              join b in ketqua on a.ID_HDON equals b.ID_HDON
                              select new
                              {

                                  a.ID_HDON,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                                  b.NGAY_CHAMNO,
                                  b.MA_KHANG,
                                  b.TIEN_NOP,
                                  b.THUE_NOP,
                                  b.TTIEN,
                                  b.SO_BBGIAO,
                                  b.MA_TNGAN,
                                  a.MA_SOGCS,
                                  b.THANG_HT

                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);
                    return ds;
                }
                else return null;
            }
            else return null;
        }

        public DataSet ChamnoOff_Nutcha(string ma_tngan, DateTime ngaybd, DateTime ngaykt, int thanght, string ma_dvql)
        {
            DataSet ds = new DataSet();
            Int64[] id_hdon;
            Int64[] so_bbgiao;

            id_hdon = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                       where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght
                       select a.ID_HDON).ToArray();
            if (id_hdon.Count() > 0)
            {
                var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                 where id_hdon.Contains(a.ID_HDON) && a.MA_DVIQLY == ma_dvql
                                 select a).ToList();
                var chamno_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                      where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght && a.MA_DVIQLY == ma_dvql
                                      select a).ToList();
                so_bbgiao = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                             where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght
                             select a.SO_BBGIAO).ToArray();
                if (so_bbgiao.Count() > 0)
                {
                    var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                                  where so_bbgiao.Contains(a.SO_BBGIAO)
                                  select new { a.MA_SOGCS, a.ID_HDON }).ToList();
                    var ketqua = (from a in chamno_offline
                                  join b in theodoino on a.ID_HDON equals b.ID_HDON
                                  where a.MA_DVIQLY == ma_dvql
                                  select new
                                  {
                                      b.ID_HDON,
                                      b.TEN_KHANG,
                                      b.DCHI_KHANG,
                                      a.MA_KHANG,
                                      a.TIEN_NOP,
                                      a.THUE_NOP,
                                      a.NGAY_CHAMNO,
                                      a.THANG_HT,
                                      TTIEN = a.TIEN_NOP + a.THUE_NOP,
                                      a.SO_BBGIAO
                                  }).ToList();
                    var kq = (from a in bbgiao
                              join b in ketqua on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  a.ID_HDON,
                                  b.TIEN_NOP,
                                  b.THUE_NOP,
                                  b.SO_BBGIAO,
                                  b.NGAY_CHAMNO,
                                  b.THANG_HT,
                                  b.TTIEN,
                                  a.MA_SOGCS,
                                  b.MA_KHANG,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);
                    return ds;
                }
                return null;
            } return null;
        }

        public DataSet ChamnoOff_Nutcha2(int bb_giao, string ma_tngan, DateTime ngaybd, DateTime ngaykt, int thanght, string ma_dvql)
        {
            DataSet ds = new DataSet();
            Int64[] id_hdon;
            Int64[] so_bbgiao;

            id_hdon = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                       where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght && a.SO_BBGIAO == bb_giao
                       select a.ID_HDON).ToArray();

            var chamno_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght && a.SO_BBGIAO == bb_giao
                                  select a).ToList();

            if (id_hdon.Count() > 0)
            {
                var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                 where id_hdon.Contains(a.ID_HDON)
                                 select a).ToList();

                var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                              where a.SO_BBGIAO == bb_giao
                              select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();

                var ketqua = (from a in chamno_offline
                              join b in theodoino on a.ID_HDON equals b.ID_HDON
                              select new
                              {
                                  b.ID_HDON,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                                  a.MA_KHANG,
                                  a.TIEN_NOP,
                                  a.THUE_NOP,
                                  a.NGAY_CHAMNO,
                                  a.THANG_HT,
                                  TTIEN = a.TIEN_NOP + a.THUE_NOP
                              }).ToList();
                var kq = (from a in bbgiao
                          join b in ketqua on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              a.SO_BBGIAO,
                              a.ID_HDON,
                              b.TIEN_NOP,
                              b.THUE_NOP,
                              b.NGAY_CHAMNO,
                              b.THANG_HT,
                              b.TTIEN,
                              a.MA_SOGCS,
                              b.MA_KHANG,
                              b.TEN_KHANG,
                              b.DCHI_KHANG
                          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                ds.Tables.Add(dt);
                return ds;
            }
            return null;
        }

        public DataSet ChamnoOff_Nutcon(string so_gcs, int bb_giao, string ma_tngan, DateTime ngaybd, DateTime ngaykt, int thanght, string ma_dvql)
        {
            DataSet ds = new DataSet();
            Int64[] id_hdon;
            Int64[] so_bbgiao;

            id_hdon = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                       where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght && a.SO_BBGIAO == bb_giao
                       select a.ID_HDON).ToArray();

            var chamno_offline = (from a in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                  where a.NGAY_CHAMNO >= ngaybd && a.NGAY_CHAMNO <= ngaykt && a.MA_TNGAN == ma_tngan && a.THANG_HT == thanght && a.SO_BBGIAO == bb_giao
                                  select a).ToList();

            if (id_hdon.Count() > 0)
            {
                var theodoino = (from a in CMIS2.DB.CN_THEODOINO
                                 where id_hdon.Contains(a.ID_HDON)
                                 select a).ToList();

                var bbgiao = (from a in CMIS2.DB.CN_HDON_GTHU
                              where a.MA_SOGCS == so_gcs
                              select new { a.MA_SOGCS, a.ID_HDON, a.SO_BBGIAO }).ToList();

                var ketqua = (from a in chamno_offline
                              join b in theodoino on a.ID_HDON equals b.ID_HDON
                              where a.MA_DVIQLY == ma_dvql
                              select new
                              {
                                  b.ID_HDON,
                                  b.TEN_KHANG,
                                  b.DCHI_KHANG,
                                  a.MA_KHANG,
                                  a.TIEN_NOP,
                                  a.THUE_NOP,
                                  a.NGAY_CHAMNO,
                                  a.THANG_HT,
                                  TTIEN = a.TIEN_NOP + a.THUE_NOP
                              }).ToList();
                var kq = (from a in ketqua
                          join b in bbgiao on a.ID_HDON equals b.ID_HDON
                          select new
                          {
                              a.ID_HDON,
                              a.TEN_KHANG,
                              a.DCHI_KHANG,
                              a.MA_KHANG,
                              a.TIEN_NOP,
                              a.THUE_NOP,
                              a.NGAY_CHAMNO,
                              a.THANG_HT,
                              a.TTIEN,
                              b.MA_SOGCS,
                              b.SO_BBGIAO
                          }).ToList();
                //var kq = (from a in chamno_offline
                //          join b in theodoino on a.ID_HDON equals b.ID_HDON
                //          select new
                //          {
                //              SO_BBGIAO = bb_giao,
                //              a.ID_HDON,
                //              TIEN_NOP = a.TIEN_NOP,
                //              THUE_NOP = a.THUE_NOP,
                //              a.NGAY_CHAMNO,
                //              THANG_HT = thanght,
                //              TTIEN = a.TIEN_NOP + a.THUE_NOP,
                //              MA_SOGCS = so_gcs,
                //              b.MA_KHANG,
                //              b.TEN_KHANG,
                //              b.DCHI_KHANG
                //          }).ToList();
                DataTable dt = Utility.LINQToDataTable(kq);
                ds.Tables.Add(dt);
                return ds;
            }
            return null;
        }

        public DataSet DS_BBG(string ma_tngan, string ma_dvqly)
        {
            DataSet ds = new DataSet();
            Int64[] so_bbgiao;
            Int64[] id_hoadon;
            so_bbgiao = (from a in CMIS2.DB.CN_BB_GTHU
                         where a.MA_TNGAN == ma_tngan && a.MA_DVIQLY == ma_dvqly
                         select a.SO_BBGIAO).ToArray();
            if (so_bbgiao.Count() > 0)
            {
                id_hoadon = (from b in CMIS2.DB.CN_HDON_GTHU
                             where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                             select b.ID_HDON).ToArray();
                if (id_hoadon.Count() > 0)
                {
                    var id = (from b in CMIS2.DB.CN_HDON_GTHU
                              where so_bbgiao.Contains(b.SO_BBGIAO) && b.MA_DVIQLY == ma_dvqly
                              select new { b.ID_HDON, b.SO_BBGIAO }).ToList();

                    var ketqua = (from e in CMIS2.DB.CN_THEODOINO
                                  where id_hoadon.Contains(e.ID_HDON) && e.TTRANG_SSAI != 2 && e.TIEN_NO > 0 && e.MA_DVIQLY == ma_dvqly
                                  select new
                                  {
                                      e.KY,
                                      e.MA_SOGCS,
                                      TTIEN_NOP = e.TIEN_PSINH + e.THUE_PSINH,
                                      TIEN_NOP = e.TIEN_PSINH,
                                      THUE_NOP = e.THUE_PSINH,
                                      e.ID_HDON,
                                      e.THANG,
                                      e.NAM,
                                      e.MA_KHANG,
                                      e.LOAI_HDON,
                                      e.TEN_KHANG,
                                      e.DCHI_KHANG
                                  }).ToList();
                    var kq = (from c in ketqua
                              join d in id on c.ID_HDON equals d.ID_HDON
                              select new
                              {
                                  d.SO_BBGIAO,
                                  c.MA_SOGCS,
                                  c.TTIEN_NOP,
                                  c.TIEN_NOP,
                                  c.THUE_NOP,
                                  c.ID_HDON,
                                  c.THANG,
                                  c.NAM,
                                  c.MA_KHANG,
                                  c.LOAI_HDON,
                                  c.TEN_KHANG,
                                  c.DCHI_KHANG,
                                  c.KY
                              }).ToList();
                    DataTable dt = Utility.LINQToDataTable(kq);
                    ds.Tables.Add(dt);
                    return ds;
                }
                else return null;
            }
            else return null;
        }

        public DataSet DS_SoGCS(int ma_bbg, string ma_tngan)
        {
            DataSet ds = new DataSet();
            Int64[] bb_giao;
            bb_giao = (from a in CMIS2.DB.CN_BB_GTHU
                       where a.MA_TNGAN == ma_tngan && a.SO_BBGIAO == ma_bbg
                       select a.SO_BBGIAO).ToArray();
            var so_gcs = (from b in CMIS2.DB.CN_HDON_GTHU
                          where bb_giao.Contains(b.SO_BBGIAO)
                          select new { b.MA_SOGCS }).ToList();

            DataTable dt = Utility.LINQToDataTable(so_gcs);
            ds.Tables.Add(dt);
            return ds;
        }

        public DataSet DS_TNV()
        {
            DataSet ds = new DataSet();
            var tngan = (from a in CMIS2.DB.D_TNGAN_VIEN
                         select a).ToList();
            var bbg = (from a in CMIS2.DB.CN_BB_GTHU
                       join b in tngan on a.MA_TNGAN equals b.MA_TNGAN
                       select new
                       {
                           b.TEN_TNGAN,
                           b.MA_TNGAN,
                           a.SO_BBGIAO
                       }).ToList();
            var ketqua = (from a in bbg
                          join b in CMIS2.DB.CN_HDON_GTHU on a.SO_BBGIAO equals b.SO_BBGIAO
                          select new { a.TEN_TNGAN, a.MA_TNGAN, a.SO_BBGIAO, b.MA_SOGCS }).ToList();
            DataTable dt = Utility.LINQToDataTable(ketqua);
            ds.Tables.Add(dt);
            return ds;
        }
        #endregion

        #endregion
    }
}
